package com.capgemini.poste;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Locale;
import java.util.Map;
import java.util.Properties;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.eclipse.persistence.config.PersistenceUnitProperties;

import com.capgemini.poste.dao.Event;
import com.capgemini.poste.dao.Installation;
import com.capgemini.poste.dao.Patch;
import com.capgemini.poste.dao.Securite;
import com.capgemini.poste.dao.Signature;
import com.capgemini.poste.metier.Definition;
import com.capgemini.poste.metier.Hotfix;
import com.capgemini.poste.metier.Login;
import com.capgemini.poste.metier.Owner;
import com.capgemini.poste.metier.Produit;
import com.capgemini.poste.metier.Workstation;

public class Main {

	static String pc;
	static String user;
	static String ip;
	static String label;
	static Date	  date ;

	static private EntityManagerFactory emf;

	/**
	 * @param args
	 */
	public static void main(String[] args) {

		// Get parameters
		// -----------------------------------------------------
		// Get connection parameters
		Properties pf = new Properties () ;
		try {
			pf.load(new FileInputStream(args[0])) ;
		} catch (FileNotFoundException e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		} catch (IOException e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		}
			
		// Catch the file name to inject
		DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT, Locale.FRANCE) ;
		String fileName = new String(args[1]);
		try {
			date = df.parse(args[2]) ;
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			System.out.println ( "Date : " + args[2] ) ;
			return ;
		}
				
		// TODO Auto-generated method stub
		ArrayList<Event> events = new ArrayList<Event> () ;

		
		/*-- Create the persitence manager --*/
		Map <String, String> props = new HashMap <String, String> () ;
		props.put(PersistenceUnitProperties.JDBC_USER, pf.getProperty("jdbc.user")) ;
		props.put(PersistenceUnitProperties.JDBC_PASSWORD, pf.getProperty("jdbc.password")) ;
		props.put(PersistenceUnitProperties.JDBC_URL, pf.getProperty("jdbc.url")) ;
		props.put(PersistenceUnitProperties.JDBC_DRIVER, pf.getProperty("jdbc.driver")) ;
		
		emf = Persistence.createEntityManagerFactory("posteCdS", props);
		EntityManager em = emf.createEntityManager();

		// open the transaction
		em.getTransaction().begin();

		// Create workstation, login, Definition
		Workstation workstation = new Workstation(em);
		Login login = new Login(em);
		Definition definition = new Definition (em) ;
		Produit produit = new Produit (em) ;
		Hotfix hotfix = new Hotfix (em) ;
		Owner owner = new Owner ( em ) ;
		
		System.out.println(fileName);
		System.out.println(date.toString()) ;

		/*-- Open the Excel file --*/
		try {
			FileInputStream file = new FileInputStream(fileName);
			XSSFWorkbook excelWorkBook = new XSSFWorkbook(file);

			// Retrieve workstation information from excel workbook
			events = workstation.retrieve(excelWorkBook) ;
			
			// Retrieve users from excel workbook
			events.addAll(login.retrieve(excelWorkBook)) ;
			
			// TODO For the software sheet, retrieve all softwares
			events.addAll(produit.retrieve (excelWorkBook)) ;
			
			// TODO for the security patch, retrieve all patches
			events.addAll(hotfix.retrieve(excelWorkBook)) ;

			// TODO for the antivirus, retrieve all definition
			events.addAll(definition.retrieve(excelWorkBook)) ;

			events.addAll(owner.retrieve(excelWorkBook, date)) ;
			
			System.out.println("Nombre de postes     : " + workstation.getSize());
			System.out.println("Nombre d'utilisateur : " + login.getSize());
			System.out.println("Nombre de definition : " + definition.getSize());
			System.out.println("Nombre de produit    : " + produit.getSize());
			System.out.println("Nombre de hotfix     : " + hotfix.getSize());
			System.out.println("Nombre d'affecttion  : " + owner.getSize());

			
			// Make link between workstation and antivirus definition
			workstationAvDefinition ( excelWorkBook, date, em, login, workstation, definition ) ;
			workstationPatch ( excelWorkBook, date, em, login, workstation, hotfix ) ;
			workstationInstalation ( excelWorkBook, date, em, login, workstation, produit ) ;
			
			// Manage events
			Event event = new Event () ;
			for ( Iterator<Event> e = events.iterator(); e.hasNext() ;) {
				event = e.next() ;
				event.setDt(date) ;
				em.persist(event) ;
				}
												
			// persists records
			workstation.save(em);
			login.save(em) ;
			definition.save(em) ;
			produit.save(em) ;
			hotfix.save(em);
			owner.save(em) ;

		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		em.getTransaction().commit();
		em.close();

	}

	private static void workstationInstalation(XSSFWorkbook excelWorkBook,
			Date date2, EntityManager em, Login login, Workstation workstation,
			Produit produit) {
		// TODO Auto-generated method stub
		XSSFRow row;
		boolean bFirst = true ;	
		
		for (Iterator<Row> rowIterator = excelWorkBook.getSheetAt(0).rowIterator(); rowIterator.hasNext();) {
			row = (XSSFRow) rowIterator.next();
            // Skip the header row
			if ( bFirst == true) {
               row = (XSSFRow) rowIterator.next();
               bFirst = false ;
            }
			
		 Installation installation = new Installation() ;
			
			// Colonne 3 = Definition
			installation.setSoftwareNb(produit.get(row.getCell(3).getStringCellValue().toUpperCase()).getNb()) ;
			// Colonne 0 = poste
			if ( row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_NUMERIC ){
				installation.setPosteCd(String.valueOf(row.getCell(0).getNumericCellValue()));
			}
			else {	
				installation.setPosteCd(row.getCell(0).getStringCellValue().toUpperCase());
			}

			installation.setDt(date2) ;

			// Persist
			em.persist(installation) ;

		}
	}

	private static void workstationPatch(XSSFWorkbook excelWorkBook, Date date2, EntityManager em, Login login, Workstation workstation, Hotfix hotfix) {
		// TODO Auto-generated method stub
		XSSFRow row;
		boolean bFirst = true ;	
		
		for (Iterator<Row> rowIterator = excelWorkBook.getSheetAt(1).rowIterator(); rowIterator.hasNext();) {
			row = (XSSFRow) rowIterator.next();
            // Skip the header row
			if ( bFirst == true) {
               row = (XSSFRow) rowIterator.next();
               bFirst = false ;
            }
			
			Patch patch = new Patch() ;
			Securite s = new Securite();
			if ( row.getCell(3).getCellType() == HSSFCell.CELL_TYPE_NUMERIC ){
				s = hotfix.get(String.valueOf(row.getCell(3).getNumericCellValue()));
			}
			else {	
				s = hotfix.get(row.getCell(3).getStringCellValue().toUpperCase());
			}
				
			// Colonne 3 = Definition
			patch.setSecuriteNb(s.getNb()) ;
			// Colonne 0 = poste
			if ( row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_NUMERIC ){
				patch.setPosteCd(String.valueOf(row.getCell(0).getNumericCellValue()));
			}
			else {	
				patch.setPosteCd(row.getCell(0).getStringCellValue().toUpperCase());
			}
			patch.setDt(date2) ;

			// Persist
			em.persist(patch) ;

		}
	}

	private static void workstationAvDefinition(XSSFWorkbook excelWorkBook,Date date2, EntityManager em, Login login, Workstation workstation, Definition definition ) {
		// TODO Auto-generated method stub
		XSSFRow row;
		boolean bFirst = true ;	
		
		for (Iterator<Row> rowIterator = excelWorkBook.getSheetAt(2).rowIterator(); rowIterator.hasNext();) {
			row = (XSSFRow) rowIterator.next();
            // Skip the header row
			if ( bFirst == true) {
               row = (XSSFRow) rowIterator.next();
               bFirst = false ;
            }
			
			Signature signature = new Signature() ;
			
			// Colonne 3 = Definition
			signature.setAntivirusNb(definition.get(row.getCell(3).getStringCellValue().toUpperCase()).getNb()) ;
			// Colonne 0 = poste
			if ( row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_NUMERIC ){
				signature.setPosteCd(String.valueOf(row.getCell(0).getNumericCellValue()));
			}
			else {	
				signature.setPosteCd(row.getCell(0).getStringCellValue().toUpperCase());
			}
			
			signature.setDt(date2) ;

			// Persist
			em.persist(signature) ;

		}
		
	}

}
